package com.hfad.todolistapp.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.DateFormat;
import android.util.Log;

import com.google.gson.Gson;
import com.hfad.todolistapp.db.model.TodoItemTable;
import com.hfad.todolistapp.model.TodoItem;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class SqlHelper extends SQLiteOpenHelper {
    private static final String TAG = "TodoListApp_SqlHelper";

    private static final String DB_NAME = "TodoListApp.db";
    private static final int DB_VERSION = 1;

    public SqlHelper(Context context) {
        this(context, DB_NAME, null, DB_VERSION);
    }

    public SqlHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("Create Table If Not Exists " + TodoItemTable.TableName + "(" + 
            TodoItemTable.ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            TodoItemTable.Title + " text Not Null," + 
            TodoItemTable.Description + " text Not Null," +
            TodoItemTable.Handler + " text Not Null," + 
            TodoItemTable.CreatedTime + " Long Not Null," + 
            TodoItemTable.Status + " text Not Null" + ")");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade: " + oldVersion + " => " + newVersion);
    }

    private void addTableColumn(SQLiteDatabase db, String tableName, String columnName, String columnValueType) {
        boolean checkColumnExist = checkColumnExist(db, tableName, columnName);
        if (!checkColumnExist) {
            db.execSQL("Alter table " + tableName + " ADD COLUMN " + columnName + columnValueType);
        }
    }

    private boolean checkColumnExist(SQLiteDatabase db, String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;
        try {    
            cursor = db.rawQuery("select * from sqlite_master where name = ? and sql like ?"
                    , new String[]{tableName, "%" + columnName + "%"});
            result = null != cursor && cursor.moveToFirst();
        } catch (Exception e) {
            Log.e(TAG, "checkColumnExists..." + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return result;
    }

    public void clearDB(SQLiteDatabase dbWriter) {
        dbWriter.execSQL("BEGIN");
        clearDBinfo(dbWriter, TodoItemTable.TableName);
        dbWriter.execSQL("COMMIT");
    }

    public void clearDBinfo(SQLiteDatabase dbWriter, String dbName) {
        dbWriter.execSQL("DELETE FROM " + dbName);
    }

    public synchronized long addTodoItem(SQLiteDatabase db, TodoItem data) {
        ContentValues values = new ContentValues();
        values.put(TodoItemTable.Title, data.getTitle());
        values.put(TodoItemTable.Description, data.getDescription());
        values.put(TodoItemTable.Handler, data.getHandler());
        values.put(TodoItemTable.CreatedTime, data.getCreatedTime());
        values.put(TodoItemTable.Status, data.getStatus().toString());

        return db.insert(TodoItemTable.TableName, null, values);
    }

    public synchronized int deleteTodoItem(SQLiteDatabase db, int id) {
        return db.delete(TodoItemTable.TableName, TodoItemTable.ID + "=?", new String[]{ String.valueOf(id) });
    }

    public synchronized long updateTodoItem(SQLiteDatabase db, TodoItem data) {
        ContentValues values = new ContentValues();
        values.put(TodoItemTable.ID, data.getId());
        values.put(TodoItemTable.Title, data.getTitle());
        values.put(TodoItemTable.Description, data.getDescription());
        values.put(TodoItemTable.Handler, data.getHandler());
        values.put(TodoItemTable.CreatedTime, data.getCreatedTime());
        values.put(TodoItemTable.Status, data.getStatus().toString());

        return db.update(TodoItemTable.TableName, values, TodoItemTable.ID + "=?", new String[]{ String.valueOf(data.getId()) });
    }

    public List<TodoItem> getTodoItemListOrderByCreatedTime(SQLiteDatabase dbReader, boolean isASC) {
      List<TodoItem> todoItemList = new ArrayList<>();
        String sql = "SELECT * FROM " + TodoItemTable.TableName + " ORDER BY " + TodoItemTable.CreatedTime + (isASC ? " ASC" : " DESC");
        Cursor cursor = dbReader.rawQuery(sql, new String[]{});
        while (cursor.moveToNext()) {
            TodoItem item = new TodoItem();
            item.setId(cursor.getInt(cursor.getColumnIndex(TodoItemTable.ID)));
            item.setTitle(cursor.getString(cursor.getColumnIndex(TodoItemTable.Title)));
            item.setDescription(cursor.getString(cursor.getColumnIndex(TodoItemTable.Description)));
            item.setHandler(cursor.getString(cursor.getColumnIndex(TodoItemTable.Handler)));
            item.setCreatedTime(cursor.getLong(cursor.getColumnIndex(TodoItemTable.CreatedTime)));
            TodoItem.ItemStatus status = TodoItem.ItemStatus.valueOf(cursor.getString(cursor.getColumnIndex(TodoItemTable.Status)));
            item.setStatus(status);
            todoItemList.add(item);
        }
        return todoItemList;
    }

    public TodoItem getTodoItemById(SQLiteDatabase dbReader, int id) {
        String sql = "SELECT * FROM " + TodoItemTable.TableName + " WHERE " + TodoItemTable.ID + "=?";
        Cursor cursor = dbReader.rawQuery(sql, new String[]{ String.valueOf(id) });
        if (cursor.moveToNext()) {
            TodoItem item = new TodoItem();
            item.setId(cursor.getInt(cursor.getColumnIndex(TodoItemTable.ID)));
            item.setTitle(cursor.getString(cursor.getColumnIndex(TodoItemTable.Title)));
            item.setDescription(cursor.getString(cursor.getColumnIndex(TodoItemTable.Description)));
            item.setHandler(cursor.getString(cursor.getColumnIndex(TodoItemTable.Handler)));
            item.setCreatedTime(cursor.getLong(cursor.getColumnIndex(TodoItemTable.CreatedTime)));
            TodoItem.ItemStatus status = TodoItem.ItemStatus.valueOf(cursor.getString(cursor.getColumnIndex(TodoItemTable.Status)));
            item.setStatus(status);
            Log.e(TAG, "getTodoItemById: " + new Gson().toJson(item));
            return item;
        } else {
            return null;
        }
    }
}